import pandas as pd
import os
import sqlparse
import sys
from pathlib import Path
from os.path import dirname

import requests
path = dirname(str(Path(__file__).resolve().parents[2]))
if path not in sys.path:
    sys.path.append(path)
from xkw_data_query.config_parse import get_bapi_config


class MysqlCaller:
    """
    sql审核平台调用工具
    """
    def __init__(self, instance_name: str, db_name: str):
        cookie, csrftoken = self.login()
        self.url = "https://opsdba.xkw.com/query/"
        self.headers = {
            "accept": "application/json, text/javascript, */*; q=0.01",
            "accept-encoding": "gzip, deflate, br, zstd",
            "accept-language": "zh-CN,zh;q=0.9",
            "connection": "keep-alive",
            "content-length": "416",
            "content-type": "application/x-www-form-urlencoded; charset=UTF-8",
            "cookie": cookie,
            "host": "opsdba.xkw.com",
            "origin": "https://opsdba.xkw.com",
            "referer": "https://opsdba.xkw.com/sqlquery/",
            "sec-ch-ua": '"Chromium";v="134", "Not:A-Brand";v="24", "Google Chrome";v="134"',
            "sec-ch-ua-mobile": "?0",
            "sec-ch-ua-platform": '"macOS"',
            "sec-fetch-dest": "empty",
            "sec-fetch-mode": "cors",
            "sec-fetch-site": "same-origin",
            "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36",
            "x-csrftoken": csrftoken,
            "x-requested-with": "XMLHttpRequest",
        }
        self.data = {
            "instance_name": instance_name,
            "db_name": db_name,
            "schema_name": "",
            "tb_name": "",
            "limit_num": 5000,
        }

    def login(self):
        
        session = requests.Session()
        res1 = session.get("https://opsdba.xkw.com/login/")
        res1.encoding = "utf-8"
        if res1.status_code != 200:
            raise Exception(f"登录失败, code: {res1.status_code}, text: {res1.text}")
        csrftoken = res1.headers["Set-Cookie"].split("csrftoken=")[1].split(";")[0]

        url = "https://opsdba.xkw.com/authenticate/"
        data = {
            "username": "zhangli02",
            "password": '5Bf1O{4"m_g.Cq0',
        }
        headers = {
            "accept": "application/json, text/javascript, */*; q=0.01",
            "x-csrftoken": csrftoken,
        }
        response = session.post(url=url, data=data, headers=headers)
        response.encoding = "utf-8"
        if response.status_code != 200:
            print(f"登录失败, code: {response.status_code}, text: {response.text}")
            raise Exception(f"登录失败, code: {response.status_code}, text: {response.text}")
        sessionid = session.cookies.get_dict()["sessionid"];
        cookie = f"xkw-device-id=E75F6C2A25254E577D49498F723C0AD8; UT1=ut-713951-TwJC0dbkLFSXyw; xk.identity=%7b%7d; csrftoken={csrftoken}; sessionid={sessionid}"
        return cookie, csrftoken

    def query(self, sql_content):
        """
        执行sql语句, 不能使用用select *，需明确指定返回字段
        返回值为列表，列表中每个元素是dict字典，字典的key是查询字段，value是查询结果
        """
        # 使用sqlparse解析sql，获取返回的字段
        sql_content = sqlparse.format(sql_content, strip_comments=True, strip_whitespace=True)

        parsed = sqlparse.parse(sql_content)
        stmt = parsed[0]
        if not stmt.get_type() == 'SELECT':
            raise Exception("sql语句中没有select语句")
        
        fields = []
        for token in stmt.tokens:
            if isinstance(token, sqlparse.sql.IdentifierList):
                fields.extend([field.get_real_name() for field in token.get_identifiers()]) 

        data = self.data
        data["sql_content"] = sql_content
        response = requests.post(url=self.url, headers=self.headers, data=data)
        res_obj = response.json()
        if res_obj["status"] != 0:
            print("查询失败", res_obj["msg"])
            return []
        ret_data = res_obj["data"]
        if ret_data["affected_rows"] == 0:
            return []
        
        ret_list = []
        for row in ret_data["rows"]:
            for i, field in enumerate(fields):
                ret_list.append({
                    field: row[i]
                })
        return ret_list
    
    def query_from_db(self, sql_content):
        """
        执行sql语句
        返回值为列表，列表中每个元素按查询字段顺序排列
        不建议用select *
        """
        data = self.data
        data["sql_content"] = sql_content
        response = requests.post(url=self.url, headers=self.headers, data=data)
        res_obj = response.json()
        if res_obj["status"] != 0:
            print("查询失败", res_obj["msg"])
            return []
        ret_data = res_obj["data"]
        if ret_data["affected_rows"] == 0:
            return []
        return ret_data["rows"]

def query_from_rbm(sql_content):
    ops_dba_caller = MysqlCaller("base-RBM-10.111.109.142-3307-查询", "rbm")
    res = ops_dba_caller.query(sql_content)
    return res    

if __name__ == "__main__":
    res = query_from_rbm("select id,resource_id,file_id  from resource_file_material order by id desc limit 10")
    print(res)
